package ATM_mysql;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBUtil {
    /**
     * 建立一个连接
     */

    public Connection getConnection() {
        // 1. 加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 创建连接
            Connection con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/lesson3?userSSL=true&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT",
                    "root", "555430");
            return con;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 关闭连接
     */
    public void closeConnection(Connection con, Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 关闭连接
     */
    public void closeConnection(Connection con, Statement statement, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            closeConnection(con, statement);
        }
    }

    /**
     * 修改一个用户数据
     */
    public void update(User user) {
        //1. 建立连接
        Connection con = getConnection();
        PreparedStatement statement = null;
        String update = "update t_user set u_password=?,u_balance=? where u_id=?";
        //2. 获取执行SQL语句执行对象
        try {
            statement = con.prepareStatement(update);
            statement.setObject(1,user.getPwd());
            statement.setObject(2,user.getBalance());
            statement.setObject(3,user.getId());
            //3. 执行SQL语句
            int row = statement.executeUpdate();
            System.out.println("影响行数：" + row);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭连接
            closeConnection(con, statement);
        }
    }

    /**
     * 修改两个个用户数据
     */
    public void update(User user1,User user2) {
        //1. 建立连接
        Connection con = getConnection();
        PreparedStatement statement = null;
        String update = "update t_user set u_password=?,u_balance=? where u_id=?";
        //2. 获取执行SQL语句执行对象
        try {
            con.setAutoCommit(false);
            statement = con.prepareStatement(update);
            statement.setObject(1,user1.getPwd());
            statement.setObject(2,user1.getBalance());
            statement.setObject(3,user1.getId());
            statement.executeUpdate();

            statement.setObject(1,user2.getPwd());
            statement.setObject(2,user2.getBalance());
            statement.setObject(3,user2.getId());
            statement.executeUpdate();
            con.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } finally {
            //关闭连接
            closeConnection(con, statement);
        }
    }


    /**
     * 添加用户数据
     */
    public void insert(User user) {
        //1. 建立连接
        Connection con = getConnection();
        PreparedStatement statement = null;
        String sql = "insert into t_user(u_user_name,u_password,u_balance) values (?,?,?)";
        //2. 获取执行SQL语句执行对象
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1,user.getAccount());
            statement.setObject(2,user.getPwd());
            statement.setObject(3,user.getBalance());
            //3. 执行SQL语句
            int row = statement.executeUpdate();
            System.out.println("影响行数：" + row);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭连接
            closeConnection(con, statement);
        }
    }

    /**
     * 查询改据库 用户名、密码
     */
    public User queryByNameAndPwd(String name, String password) {
        //1. 建立连接
        Connection con = getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        User user = null;
        String sql = "select * from t_user where u_user_name=? and u_password=?";
        //2. 获取执行SQL语句执行,并返回结果
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1,name);
            statement.setObject(2,password);
            //3. 执行SQL语句
            rs = statement.executeQuery();
            if (rs.next()) {
                user = new User();
                user.setId(rs.getInt("u_id"));
                user.setAccount(rs.getString("u_user_name"));
                user.setPwd(rs.getString("u_password"));
                user.setBalance(rs.getDouble("u_balance"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭连接
            closeConnection(con, statement, rs);
        }
        return user;
    }

    /**
     * 查询改据库ID
     */
    public User queryById(int id) {
        //1. 建立连接
        Connection con = getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        User user = null;
        String sql = "select * from t_user where u_id=?";
        //2. 获取执行SQL语句执行,并返回结果
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1,id);
            //3. 执行SQL语句
            rs = statement.executeQuery();
            if (rs.next()) {
                user = new User();
                user.setId(rs.getInt("u_id"));
                user.setAccount(rs.getString("u_user_name"));
                user.setPwd(rs.getString("u_password"));
                user.setBalance(rs.getDouble("u_balance"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭连接
            closeConnection(con, statement, rs);
        }
        return user;
    }

    /**
     * 查询数据库 用户名
     */
    public User queryByName(String name) {
        //1. 建立连接
        Connection con = getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        User user = null;
        String sql = "select * from t_user where u_user_name=?";
        //2. 获取执行SQL语句执行,并返回结果
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1,name);
            //3. 执行SQL语句
            rs = statement.executeQuery();
            if (rs.next()) {
                user = new User();
                user.setId(rs.getInt("u_id"));
                user.setAccount(rs.getString("u_user_name"));
                user.setPwd(rs.getString("u_password"));
                user.setBalance(rs.getDouble("u_balance"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭连接
            closeConnection(con, statement, rs);
        }
        return user;
    }
}
